/**
 * Copyright 2005 The Apache Software Foundation
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.edi.db.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.edi.db.EdiDb;
import com.edi.db.dialect.function.DialectFunction;
import com.edi.db.table.FieldEntity;
import com.edi.db.table.TableEntity;
import com.edi.db.type.NullableType;
import com.edi.util.StringHelper;

public class Oracle8iDialect extends Dialect {
	private static final Log log = LogFactory.getLog(Oracle8iDialect.class);

	public Oracle8iDialect() {
		super();
		registFunction("substring", new CommonFunction(EdiDb.STRING, "substr"));
		registFunction("cast", new CommonFunction(EdiDb.FLOAT, "cast"));
		registFunction("top", new TopFunction(EdiDb.FLOAT, "top"));
		registColumnTypes();

	}

	private void registColumnTypes() {
		registerColumnType(Types.CHAR, "char(1)");
		registerColumnType(Types.VARCHAR, 4000, "varchar2($l)");
		registerColumnType(Types.VARCHAR, "varchar2($l)");

		registerColumnType(Types.BIT, "number(1,0)");
		registerColumnType(Types.BIGINT, "number(19,0)");
		registerColumnType(Types.SMALLINT, "number(5,0)");
		registerColumnType(Types.TINYINT, "number(3,0)");
		registerColumnType(Types.INTEGER, "number(10,0)");

		registerColumnType(Types.FLOAT, "float");
		registerColumnType(Types.DOUBLE, "double precision");
		registerColumnType(Types.NUMERIC, "number($p,$s)");
		registerColumnType(Types.DECIMAL, "number($p,$s)");

		registerColumnType(Types.DATE, "date");
		registerColumnType(Types.TIME, "date");
		registerColumnType(Types.TIMESTAMP, "date");

		registerColumnType(Types.VARBINARY, 2000, "raw($l)");
		registerColumnType(Types.VARBINARY, "long raw");

		registerColumnType(Types.BLOB, "blob");
		registerColumnType(Types.CLOB, "clob");
	}

	public String getCurrentTimestampSelectString() {
		return "select sysdate from dual";
	}

	/**
	 * 将eql翻译成sql
	 */
	public String translate(String eql) {
		if (ignoreCase)
			eql = StringUtils.lowerCase(eql);
		try {
			String[] eqls = StringHelper.split(StringHelper.WHITESPACE, eql);
			for (int i = 0; i < eqls.length; i++) {
				String word = eqls[i];
				if (word == null || "".equals(word))
					continue;
				DialectFunction function = getFunction(word);
				if (function != null) {
					function.process(eql, word, word);
				}
				int beginBrack = word.indexOf("(");
				if (beginBrack > 0) {
					String functionName = word.substring(0, beginBrack);
					function = getFunction(functionName);
					if (function == null) {
						continue;
					} else {
						function.process(eql, functionName, eqls[i]);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return eql;
	}

	/**
	 * 获取保存sql，用于批量提交
	 */
	public String getSavePreparedString(List fieldNameist, String tableName) {
		StringBuffer sql = new StringBuffer();
		try {
			sql.append("insert into ").append(tableName).append(" (");
			Iterator it = fieldNameist.iterator();
			while (it.hasNext()) {
				String fieldName = (String) it.next();
				if (!it.hasNext())
					sql.append(fieldName).append(") values (");
				else
					sql.append(fieldName).append(",");
			}
			for (int i = 0; i < fieldNameist.size(); i++) {
				if (i == fieldNameist.size() - 1)
					sql.append("?)");
				else
					sql.append("?,");

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		log.debug(" return sql =" + sql);
		return sql.toString();
	}

	public String getDeletePreparedString(TableEntity tableEntity) {
		if (tableEntity == null || tableEntity.getTableName() == null
				|| "".equals(tableEntity.getTableName()))
			throw new RuntimeException("表名为空！");
		String sql = "";

		try {
			sql = "delete " + tableEntity.getTableName() + " where ";
			List<String> keys = tableEntity.getPrimarykey();
			Iterator<String> keyIt = keys.iterator();
			while (keyIt.hasNext()) {
				String key = keyIt.next();
				sql += key + " = ? ";
				if (keyIt.hasNext())
					sql += " and " + key + " = ? ";
			}
		} catch (Exception e) {
			e.printStackTrace();

		}
		log.debug(" return sql =" + sql);
		return sql;
	}

	public String ifExitTable(String tableName) {
		String sql = "select * from USER_TABLES where table_name = '"
				+ StringUtils.upperCase(tableName) + "'";
		return sql;
	}

	public String createTable(TableEntity tableEntity) {
		StringBuffer sql = new StringBuffer();
		try {
			sql.append("CREATE TABLE ").append(tableEntity.getTableName())
					.append(" (");
			Hashtable<String, FieldEntity> hashTable = tableEntity
					.getFieldEntitys();
			Set<String> fieldNames = hashTable.keySet();
			int i = 1;
			for (String fieldName : fieldNames) {
				log.debug("fileName : " + fieldName);
				FieldEntity fieldEntity = hashTable.get(fieldName);
				if (fieldEntity == null)
					continue;
				sql.append(fieldName).append(" ");
				String columnType = getTypeName(fieldEntity.getFieldType()
						.sqlType(), fieldEntity.getSize(), fieldEntity
						.getPrecision(), fieldEntity.getScale());
				log.debug("fieldType : " + columnType);
				sql.append(columnType);
				if (!fieldEntity.isNullable())
					sql.append(" not null");
				if (fieldEntity.getDefaultValue() != null
						&& !"".equals(fieldEntity.getDefaultValue()))
					sql.append(" default '").append(
							fieldEntity.getDefaultValue()).append("'");
				if (fieldEntity.isIdentify())
					sql.append(" PRIMARY KEY ");
				if (i < fieldNames.size())
					sql.append(",");
				i++;
			}

			sql.append(")");

		} catch (Exception e) {
			e.printStackTrace();
		}
		return sql.toString();
	}

	/**
	 * 返回删除表列的方言语句
	 */
	public String dropColumn(String tableName, List fieldList) {
		String sql = "";
		try {
			sql += "alter table '" + tableName + "'";
			Iterator it = fieldList.iterator();
			while (it.hasNext()) {
				FieldEntity fieldEntity = (FieldEntity) it.next();
				sql += "drop column '" + fieldEntity.getFieldName() + "'";
				if (it.hasNext())
					sql += ",";
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return sql;
	}

	/**
	 * 返回添加表列的方言语句
	 */
	public String addColumn(String tableName, List fieldList) {
		String sql = "";
		try {
			sql = "alter table '" + tableName + "' add(";
			Iterator it = fieldList.iterator();
			while (it.hasNext()) {
				FieldEntity fieldEntity = (FieldEntity) it.next();
				String columnType = getTypeName(fieldEntity.getFieldType()
						.sqlType(), fieldEntity.getSize(), fieldEntity
						.getPrecision(), fieldEntity.getScale());

				sql += fieldEntity.getFieldName() + " " + columnType;
				if (!fieldEntity.isNullable())
					sql += " not null ";
				if (it.hasNext())
					sql += ",";
			}
			sql += ")";

		} catch (Exception e) {
			e.printStackTrace();
		}
		log.debug(" sql =" + sql);
		return sql;
	}

	/**
	 * 返回修改列的方言语句
	 */
	public String modifyColumn(String tableName, List fieldList) {
		String sql = "";
		try {
			sql = "alter table '" + tableName + "'";
			Iterator it = fieldList.iterator();
			while (it.hasNext()) {
				FieldEntity fieldEntity = (FieldEntity) it.next();
				String columnType = getTypeName(fieldEntity.getFieldType()
						.sqlType(), fieldEntity.getSize(), fieldEntity
						.getPrecision(), fieldEntity.getScale());
				sql += "alter column '" + fieldEntity.getFieldName() + "' "
						+ columnType + "";
				if (it.hasNext())
					sql += ",";
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		log.debug(" sql =" + sql);
		return sql;
	}

	/**
	 * top函数转换
	 * 
	 * @author cya select top 10 id,name,info from jc_user where nickname like
	 *         '%1%' order by id
	 * 
	 */
	class TopFunction extends DialectFunction {
		private NullableType type;
		private String name;

		public TopFunction(NullableType type, String name) {
			this.type = type;
			this.name = name;
		}

		public String process(String eql, String functionName, String function) {
			StringBuffer sbuffer = new StringBuffer();
			sbuffer.append(eql);
			int topStart = sbuffer.indexOf("top");
			int fromStart = sbuffer.indexOf("from");
			int numEnd = topStart;
			String tempNum = "";
			boolean numSart = false;
			for (int i = topStart; i < fromStart; i++) {
				char c = sbuffer.charAt(i);
				if (Character.isDigit(c)) {
					numSart = true;
					tempNum += c;
					numEnd = i;
				} else {
					if (numSart)
						break;
				}
			}
			if (tempNum == null || "".equals(tempNum))
				throw new RuntimeException("top 后的 记录数值为空！");
			log.debug(" top number= " + tempNum);
			sbuffer.delete(topStart, numEnd);
			eql = "select * from ( " + sbuffer.toString()
					+ ") where rownum <= " + Integer.valueOf(tempNum);

			return eql;
		}

	}

	/**
	 * oracle中decode不用转换 转换
	 * 
	 * @author cya
	 * 
	 */
	class DecodeFunction extends DialectFunction {
		public String process(String eql, String functionName, String function) {
			return eql;
		}
	}

	@Override
	public boolean isEqualType(NullableType type1, NullableType type2) {
		return type1.sqlType() == type2.sqlType();
	}

	@Override
	public NullableType getSqlType(int type, int precesion, int scale) {
		NullableType rType = EdiDb.STRING;
		switch (type) {
		case Types.LONGVARCHAR:
			rType = EdiDb.LONG;
			break;
		case Types.BINARY:
			rType = EdiDb.BINARY;
			break;
		case Types.BLOB:
			rType = EdiDb.BLOB;
			break;
		case Types.BOOLEAN:
			rType = EdiDb.BOOLEAN;
			break;
		case Types.CHAR:
			rType = EdiDb.CHARACTER;
			break;
		case Types.CLOB:
			rType = EdiDb.CLOB;
			break;
		case Types.DATE:
			rType = EdiDb.DATE;
			break;
		case Types.DECIMAL:
			rType = EdiDb.BIG_DECIMAL;
			break;
		case Types.DOUBLE:
			rType = EdiDb.DOUBLE;
			break;
		case Types.FLOAT:
			rType = EdiDb.FLOAT;
			break;
		case Types.INTEGER:
			rType = EdiDb.INTEGER;
			break;
		case Types.TIME:
			rType = EdiDb.TIME;
			break;
		case Types.TIMESTAMP:
			rType = EdiDb.DATE;
			break;
		case Types.VARCHAR:
			rType = EdiDb.STRING;
			break;
		case Types.VARBINARY:
			rType = EdiDb.BINARY;
			break;
		case -100:
			rType = EdiDb.TIMESTAMP;
			break;
		case Types.NUMERIC:
			if (precesion == 1 && scale==0)
				rType = EdiDb.BOOLEAN;
			else
			rType = EdiDb.BIG_INTEGER;
			break;
		case Types.TINYINT:
			rType = EdiDb.BOOLEAN;
		}
		return rType;
	}

	@Override
	public List<String> getPrimaryKeys(Connection conn, String tableName)
			throws SQLException {
		List<String> primaryKeys = new ArrayList<String>();
		DatabaseMetaData dbmeta = conn.getMetaData();

		if (!tableName.equalsIgnoreCase(EdiDb.TABLE_EDI_SYSTEMP)) {
			ResultSet keysRsultSet = null;
			try{
			// 获得主键信息
				keysRsultSet = dbmeta.getPrimaryKeys(null, null, tableName.toUpperCase());
			while (keysRsultSet.next()) {
				primaryKeys
						.add(keysRsultSet.getString(4).toLowerCase());
			}
			}finally{
				if (keysRsultSet != null){
					keysRsultSet.close();
				}
			}
		}
		return primaryKeys;
	}
}
